using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace Colman.Rpg.Server
{
    ///<summary>
    /// concantenations class 
    ///</summary>
    public static class Dal
    {
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
       
        /// <summary>
        /// Checks if the user's credentials are correct and if so, returns the user's <see cref="Player"/> object.
        /// </summary>
        /// <param name="username">The user logging in.</param>
        /// <param name="hashedpassword">The password, in its hashed form.</param>
        /// <returns>The user's <see cref="Player"/> object.</returns>
        public static Player Login(string username, string hashedpassword)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                #region | LoginCommand |
                SqlCommand LoginCommand = new SqlCommand(
                    "SELECT Name " +
                    "FROM   Players " +
                    "WHERE  Name = @Name             AND " +
                    "       PasswordHash = @Password", connection);

                LoginCommand.Parameters.Add("Name", SqlDbType.NVarChar, 100);
                LoginCommand.Parameters.Add("Password", SqlDbType.NVarChar);

                LoginCommand.Parameters["Name"].Value = username;
                LoginCommand.Parameters["Password"].Value = hashedpassword;
                #endregion

                connection.Open();

                string receivedName = Convert.ToString(LoginCommand.ExecuteScalar());

                if (receivedName != username)
                        return null;

                Player player = Dal.GetPlayer(username);
                player.HP = player.MaxHP; // Login with full HP (you rested)

                return player;
            }
        }

        ///<summary>
        /// update the player in the DB
        ///</summary>
        ///<param name="player">the player object</param>
        public static void UpdatePlayer(Player player)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                #region | updateUserCommand |

                SqlCommand updateUserCommand = new SqlCommand(
                    "UPDATE [Players] " +
                    "SET    [HP]     = @HP," +
                    "       [BaseAP] = @BaseAP," +
                    "       [BaseDP] = @BaseDP," +
                    "       [MaxHP]  = @MaxHP," +
                    "       [XP]     = @XP," +
                    "       [Level]  = @Level," +
                    "       [Gold]   = @Gold " +
                    "WHERE  [Name]   = @Name", connection);

                updateUserCommand.Parameters.Add("HP", SqlDbType.Int);
                updateUserCommand.Parameters.Add("BaseAP", SqlDbType.Int);
                updateUserCommand.Parameters.Add("BaseDP", SqlDbType.Int);
                updateUserCommand.Parameters.Add("MaxHP", SqlDbType.Int);
                updateUserCommand.Parameters.Add("XP", SqlDbType.Int);
                updateUserCommand.Parameters.Add("Name", SqlDbType.NVarChar, 100);
                updateUserCommand.Parameters.Add("Level", SqlDbType.Int);
                updateUserCommand.Parameters.Add("Gold", SqlDbType.Int);
                #endregion

                updateUserCommand.Parameters["Name"].Value = player.Name;
                updateUserCommand.Parameters["HP"].Value = player.HP;
                updateUserCommand.Parameters["BaseAP"].Value = player.AP;
                updateUserCommand.Parameters["BaseDP"].Value = player.DP;
                updateUserCommand.Parameters["MaxHP"].Value = player.MaxHP;
                updateUserCommand.Parameters["XP"].Value = player.XP;
                updateUserCommand.Parameters["Name"].Value = player.Name;
                updateUserCommand.Parameters["Level"].Value = player.Level;
                updateUserCommand.Parameters["Gold"].Value = player.Gold;

                connection.Open();

                updateUserCommand.ExecuteNonQuery();
            }
        }
 
        ///<summary>
        ///Add a new user to the DB.
        ///</summary>
        ///<param name="player">The player object</param>
        ///<param name="PasswordHash">The password, in its hashed form.</param>
        public static void CreatePlayer(Player player, string PasswordHash)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    #region | insertUserDataCommand |
                    SqlCommand insertUserDataCommand = new SqlCommand(
                        "insert Players " +
                        "(Name,PasswordHash,Level,XP,MaxHP,HP,BaseAP,BaseDP,Armor,Weapon,Gold)" +
                        "values (@Name,@PasswordHash,@Level,@XP,@MaxHP,@HP,@BaseAP" +
                        ",@BaseDP,@Armor,@Weapon,@Gold);", connection);

                    insertUserDataCommand.Parameters.Add("Name", SqlDbType.NVarChar, 100);
                    insertUserDataCommand.Parameters.Add("PasswordHash", SqlDbType.NVarChar);
                    insertUserDataCommand.Parameters.Add("Level", SqlDbType.Int);
                    insertUserDataCommand.Parameters.Add("XP", SqlDbType.Int);
                    insertUserDataCommand.Parameters.Add("MaxHP", SqlDbType.Int);
                    insertUserDataCommand.Parameters.Add("HP", SqlDbType.Int);
                    insertUserDataCommand.Parameters.Add("BaseAP", SqlDbType.Int);
                    insertUserDataCommand.Parameters.Add("BaseDP", SqlDbType.Int);
                    insertUserDataCommand.Parameters.Add("Armor", SqlDbType.NVarChar, 100);
                    insertUserDataCommand.Parameters.Add("Weapon", SqlDbType.NVarChar, 100);
                    insertUserDataCommand.Parameters.Add("Gold", SqlDbType.Int);
                    #endregion

                    insertUserDataCommand.Parameters["Name"].Value = player.Name;
                    insertUserDataCommand.Parameters["PasswordHash"].Value = PasswordHash;
                    insertUserDataCommand.Parameters["Level"].Value = player.Level;
                    insertUserDataCommand.Parameters["XP"].Value = player.XP;
                    insertUserDataCommand.Parameters["MaxHP"].Value = player.MaxHP;
                    insertUserDataCommand.Parameters["HP"].Value = player.HP;
                    insertUserDataCommand.Parameters["BaseAP"].Value = player.AP;
                    insertUserDataCommand.Parameters["BaseDP"].Value = player.DP;
                    insertUserDataCommand.Parameters["Armor"].Value = player.Armor.Name;
                    insertUserDataCommand.Parameters["Weapon"].Value = player.Weapon.Name;
                    insertUserDataCommand.Parameters["Gold"].Value = player.Gold;

                    connection.Open();

                    try
                    {
                        insertUserDataCommand.ExecuteNonQuery();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new InvalidOperationException("Can not create a new player. A likely cause is that the provided name is already in use.", ex);
                    }
                }

                scope.Complete();
            }
        }

        ///<summary>
        /// Gets all the user data from the DB.
        ///</summary>
        ///<param name="UserName">Inpot of the User Name.</param>
        ///<returns>Player Object.</returns>
        public static Player GetPlayer(string UserName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                #region | playerCommand |

                SqlCommand playerCommand = new SqlCommand(
                    "SELECT [Players].[Name], [Level], [XP], [MaxHP], [HP], [BaseAP], [BaseDP], [Armor], [Armors].[DefenseBonus], CONVERT(int, [Armors].[Cost] * 0.8) ArmorCost, [Weapon], [Weapons].[AttackBonus], CONVERT(int, [Weapons].[Cost] * 0.8) WeaponCost, [Gold] " +
                    "FROM   [Players], [Items] AS [Weapons], [Items] AS [Armors] " +
                    "WHERE  [Players].[Armor] = [Armors].[Name] AND " +
                    "       [Players].[Weapon] = [Weapons].[Name] AND " +
                    "       [Players].[Name] = @UserName", connection);

                playerCommand.Parameters.Add("UserName", SqlDbType.NVarChar, 100);

                playerCommand.Parameters["UserName"].Value = UserName;
                #endregion

                #region | itemsCommand |
                SqlCommand itemsCommand = new SqlCommand(
                    "SELECT [ItemName], [Amount], [Type], [ItemTypes].[Name] AS TypeName, [AttackBonus], [DefenseBonus], CONVERT(int, [Cost] * 0.8) AS [Cost] " +
                    "FROM   [PlayerItems] JOIN [Items] ON [PlayerItems].[ItemName] = [Items].[Name] JOIN [ItemTypes] ON [Type] = [ItemTypes].[Id] " +
                    "WHERE  [PlayerName] = @UserName", connection);

                itemsCommand.Parameters.Add("UserName", SqlDbType.NVarChar, 100);
                #endregion

                Player player = null;

                connection.Open();

                using (SqlDataReader drPlayers = playerCommand.ExecuteReader())
                {
                    drPlayers.Read();

                    if (!drPlayers.HasRows)
                        return null;

                    Weapon weapon = new Weapon(
                        drPlayers.GetInt32(drPlayers.GetOrdinal("AttackBonus")), 
                        drPlayers.GetString(drPlayers.GetOrdinal("Weapon")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("WeaponCost")));
                    Armor armor = new Armor(
                        drPlayers.GetInt32(drPlayers.GetOrdinal("DefenseBonus")), 
                        drPlayers.GetString(drPlayers.GetOrdinal("Armor")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("ArmorCost")));
                    
                    player = new Player(
                        drPlayers.GetString(drPlayers.GetOrdinal("Name")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("HP")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("BaseAP")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("BaseDP")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("MaxHP")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("XP")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("Level")),
                        drPlayers.GetInt32(drPlayers.GetOrdinal("Gold")),
                        weapon, armor);
                }

                itemsCommand.Parameters["UserName"].Value = UserName;

                using (SqlDataReader drItems = itemsCommand.ExecuteReader())
                {
                    while (drItems.Read())
                    {
                        int itemType = drItems.GetInt32(drItems.GetOrdinal("Type"));

                        switch (itemType)
                        {
                            case (int)ItemType.Armor:
                                player.Items.Add(new Armor(
                                    drItems.GetInt32(drItems.GetOrdinal("DefenseBonus")),
                                    drItems.GetString(drItems.GetOrdinal("ItemName")),
                                    drItems.GetInt32(drItems.GetOrdinal("Amount")),
                                    drItems.GetInt32(drItems.GetOrdinal("Cost"))));
                                break;
                            case (int)ItemType.Weapon:
                                player.Items.Add(new Weapon(
                                    drItems.GetInt32(drItems.GetOrdinal("AttackBonus")),
                                    drItems.GetString(drItems.GetOrdinal("ItemName")),
                                    drItems.GetInt32(drItems.GetOrdinal("Amount")),
                                    drItems.GetInt32(drItems.GetOrdinal("Cost"))));
                                break;
                            default:
                                player.Items.Add(new MagicItem(
                                    drItems.GetInt32(drItems.GetOrdinal("DefenseBonus")),
                                    drItems.GetInt32(drItems.GetOrdinal("AttackBonus")),
                                    drItems.GetString(drItems.GetOrdinal("ItemName")),
                                    drItems.GetInt32(drItems.GetOrdinal("Cost")),
                                    drItems.GetInt32(drItems.GetOrdinal("Amount")),
                                    drItems.GetString(drItems.GetOrdinal("TypeName"))));
                                break;
                        }
                    }
                }

                return player;
            }
        }
        
        ///<summary>
        /// Gets all the items for the Shop
        ///</summary>
        ///<returns>list of MagicItem.</returns>
        public static IEnumerable<Item> GetAllItems()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                #region | GetAllItemsInShopCommand |
                SqlCommand GetAllItemsInShopCommand = new SqlCommand(
                    "SELECT   [Items].[Name] AS [ItemName], [Type], [ItemTypes].[Name] AS [TypeName], [AttackBonus], [DefenseBonus], [Cost] "+
                    "FROM     [Items] JOIN [ItemTypes] ON [Items].[Type] = [ItemTypes].[Id]" +
                    "ORDER BY [Type], [Cost];", connection);
                #endregion

                connection.Open();
                
                SqlDataReader drItems = GetAllItemsInShopCommand.ExecuteReader();

                while (drItems.Read())
                {
                    int itemType = drItems.GetInt32(drItems.GetOrdinal("Type"));

                    switch (itemType)
                    {
                        case (int)ItemType.Armor:
                            yield return new Armor(
                                drItems.GetInt32(drItems.GetOrdinal("DefenseBonus")),
                                drItems.GetString(drItems.GetOrdinal("ItemName")),
                                drItems.GetInt32(drItems.GetOrdinal("Cost")));
                            break;
                        case (int)ItemType.Weapon:
                            yield return new Weapon(
                                drItems.GetInt32(drItems.GetOrdinal("AttackBonus")),
                                drItems.GetString(drItems.GetOrdinal("ItemName")),
                                drItems.GetInt32(drItems.GetOrdinal("Cost")));
                            break;
                        default:
                            yield return new MagicItem(
                                drItems.GetInt32(drItems.GetOrdinal("DefenseBonus")),
                                drItems.GetInt32(drItems.GetOrdinal("AttackBonus")),
                                drItems.GetString(drItems.GetOrdinal("ItemName")),
                                drItems.GetInt32(drItems.GetOrdinal("Cost")),
                                1,
                                drItems.GetString(drItems.GetOrdinal("TypeName")));
                            break;
                    }
                }
            }
        }

        /// <summary>
        /// Gives the player one item, deducting the appropriate amount of gold.
        /// </summary>
        /// <param name="player">The player that has bought the item.</param>
        /// <param name="itemName">The item that is to be bought.</param>
        /// <returns>The updated player.</returns>
        public static Player PlayerBuysItem(Player player, string itemName)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    Dal.GiveItemToPlayer(connection, player, itemName, true);

                    // Make sure the player has less gold now.
                    #region | deductGoldCommand |
                    SqlCommand deductGoldCommand = new SqlCommand(
                        "UPDATE [Players] " +
                        "SET    [Gold] = [Gold] - (SELECT [Cost] " +
                        "                          FROM   [Items] " +
                        "                          WHERE  [Name] = @ItemName) " +
                        "WHERE  [Name] = @PlayerName", connection);

                    deductGoldCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
                    deductGoldCommand.Parameters.Add("ItemName", SqlDbType.NVarChar, 100);
                    #endregion

                    deductGoldCommand.Parameters["PlayerName"].Value = player.Name;
                    deductGoldCommand.Parameters["ItemName"].Value = itemName;

                    deductGoldCommand.ExecuteNonQuery();
                }

                scope.Complete();
            }

            return Dal.GetPlayer(player.Name);
        }

        private static void GiveItemToPlayer(SqlConnection connection, Player player, string itemName, bool shouldBeAbleToBuy)
        {
            // Make sure both the player and the item exist and that there is enough gold.
            #region | validityCommand |
            SqlCommand validityCommand = new SqlCommand(
                "SELECT COUNT(*) " +
                "FROM   [Items], [Players] " +
                "WHERE  [Players].[Name] =  @PlayerName    AND " +
                "       [Items].[Name]   =  @ItemName", connection);

            if (shouldBeAbleToBuy)
            {
                validityCommand.CommandText += " AND [Players].[Gold] >= [Items].[Cost]";
            }

            validityCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
            validityCommand.Parameters.Add("ItemName", SqlDbType.NVarChar, 100);
            #endregion

            validityCommand.Parameters["PlayerName"].Value = player.Name;
            validityCommand.Parameters["ItemName"].Value = itemName;

            if (Convert.ToInt32(validityCommand.ExecuteScalar()) == 0)
                throw new InvalidOperationException("Inconsistency between player and server data. Please restart the client.");

            #region | updateCommand |
            SqlCommand updateCommand = new SqlCommand(
                "UPDATE PlayerItems " +
                "SET    Amount = Amount + 1 " +
                "WHERE  PlayerName = @PlayerName AND " +
                "       ItemName   = @ItemName", connection);

            updateCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
            updateCommand.Parameters.Add("ItemName", SqlDbType.NVarChar, 100);
            #endregion

            updateCommand.Parameters["PlayerName"].Value = player.Name;
            updateCommand.Parameters["ItemName"].Value = itemName;

            // If the update was not successful, this means that we should
            // have inserted instead of updated.
            if (updateCommand.ExecuteNonQuery() == 0)
            {
                #region | insertCommand |
                SqlCommand insertCommand = new SqlCommand(
                    "INSERT INTO [PlayerItems] ([PlayerName], [ItemName], [Amount]) " +
                    "VALUES (@PlayerName, @ItemName, 1)", connection);

                insertCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
                insertCommand.Parameters.Add("ItemName", SqlDbType.NVarChar, 100);
                #endregion

                insertCommand.Parameters["PlayerName"].Value = player.Name;
                insertCommand.Parameters["ItemName"].Value = itemName;

                insertCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Takes one item from the player, adding the appropriate amount of gold.
        /// </summary>
        /// <param name="player">The player that has sold the item.</param>
        /// <param name="itemName">The item that is to be sold.</param>
        /// <returns>The updated player.</returns>
        public static Player PlayerSellsItem(Player player, string itemName)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    Dal.TakeItemFromPlayer(connection, player, itemName);

                    // Make sure the player has more gold now.
                    #region | addGoldCommand |
                    SqlCommand addGoldCommand = new SqlCommand(
                        "UPDATE [Players] " +
                        "SET    [Gold] = [Gold] + (SELECT [Cost] " +
                        "                          FROM   [Items] " +
                        "                          WHERE  [Name] = @ItemName) * 0.8 " +
                        "WHERE  [Name] = @PlayerName", connection);

                    addGoldCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
                    addGoldCommand.Parameters.Add("ItemName", SqlDbType.NVarChar, 100);
                    #endregion

                    addGoldCommand.Parameters["PlayerName"].Value = player.Name;
                    addGoldCommand.Parameters["ItemName"].Value = itemName;

                    addGoldCommand.ExecuteNonQuery();

                }

                scope.Complete();
            }

            return Dal.GetPlayer(player.Name);
        }

        private static void TakeItemFromPlayer(SqlConnection connection, Player player, string itemName)
        {
            #region | updateCommand |
            SqlCommand updateCommand = new SqlCommand(
                "UPDATE PlayerItems " +
                "SET    Amount = Amount - 1 " +
                "WHERE  PlayerName = @PlayerName AND " +
                "       ItemName   = @ItemName", connection);

            updateCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
            updateCommand.Parameters.Add("ItemName", SqlDbType.NVarChar, 100);
            #endregion

            updateCommand.Parameters["PlayerName"].Value = player.Name;
            updateCommand.Parameters["ItemName"].Value = itemName;

            // Update not successful means that the data the client has
            // is wrong.
            if (updateCommand.ExecuteNonQuery() == 0)
                throw new InvalidOperationException("Inconsistency between player and server data. Please restart the client.");

            // Clean unused records.
            #region | cleanCommand |
            SqlCommand cleanCommand = new SqlCommand(
                "DELETE FROM [PlayerItems] " +
                "WHERE       [Amount] = 0", connection);
            #endregion

            cleanCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// Equip player with new armor
        /// </summary>
        /// <param name="player">The equiped player</param>
        /// <param name="armor">The new armor</param>
        /// <returns></returns>
        public static Player EquipArmor(Player player, Armor armor)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    Dal.GiveItemToPlayer(connection, player, player.Armor.Name, false);
                    Dal.TakeItemFromPlayer(connection, player, armor.Name);

                    #region | equipArmorCommand |
                    SqlCommand equipArmorCommand = new SqlCommand(
                        "UPDATE Players " +
                        "SET    [Armor] = @ArmorName " +
                        "WHERE  [Name]  = @PlayerName", connection);

                    equipArmorCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
                    equipArmorCommand.Parameters.Add("ArmorName", SqlDbType.NVarChar, 100);
                    #endregion

                    equipArmorCommand.Parameters["PlayerName"].Value = player.Name;
                    equipArmorCommand.Parameters["ArmorName"].Value = armor.Name;

                    equipArmorCommand.ExecuteNonQuery();
                }

                scope.Complete();
            }

            return Dal.GetPlayer(player.Name);
        }

        /// <summary>
        /// Equip player with new weapon
        /// </summary>
        /// <param name="player">The equiped player</param>
        /// <param name="weapon">The new weapon</param>
        /// <returns></returns>
        public static Player EquipWeapon(Player player, Weapon weapon)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    Dal.GiveItemToPlayer(connection, player, player.Weapon.Name, false);
                    Dal.TakeItemFromPlayer(connection, player, weapon.Name);

                    #region | equipWeaponCommand |
                    SqlCommand equipWeaponCommand = new SqlCommand(
                        "UPDATE Players " +
                        "SET    [Weapon] = @WeaponName " +
                        "WHERE  [Name]  = @PlayerName", connection);

                    equipWeaponCommand.Parameters.Add("PlayerName", SqlDbType.NVarChar, 100);
                    equipWeaponCommand.Parameters.Add("WeaponName", SqlDbType.NVarChar, 100);
                    #endregion

                    equipWeaponCommand.Parameters["PlayerName"].Value = player.Name;
                    equipWeaponCommand.Parameters["WeaponName"].Value = weapon.Name;

                    equipWeaponCommand.ExecuteNonQuery();
                }

                scope.Complete();
            }

            return Dal.GetPlayer(player.Name);
        }
    }
}
